package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.ISalPostDao;
import com.boss.salary.entity.SalPersonPostRef;
import com.boss.salary.entity.SalPost;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class SalPostDaoImpl extends BaseDaoImpl implements ISalPostDao {

    @Override
    public List<SalPost> querySalPostPage(PaginationDTO paginationDTO) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select id,code,name,create_time,update_time ")
                .append(" from ").append(TableName.SAL_POST)
                .append(" where 1=1 ");
        sql.append(" order by code ");
        String querySql = this.boxUpSql(sql.toString());
        Object[] params = boxUpParam(paramList.toArray(), paginationDTO);
        return this.getjdbcTemplate().query(querySql, new BeanPropertyRowMapper<>(SalPost.class), params);
    }

    @Override
    public int querySalPostCount() {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) ")
                .append(" from ").append(TableName.SAL_POST)
                .append(" where 1=1 ");
        sql.append(" order by create_time ");
        return this.getjdbcTemplate().queryForObject(sql.toString(), paramList.toArray(), Integer.class);
    }

    @Override
    public int queryDataByCode(String code) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT COUNT(*) FROM ").append(TableName.SAL_POST);
        sql.append(" WHERE code = ? ");
        return this.getjdbcTemplate().queryForObject(sql.toString(), new Object[]{code}, Integer.class);
    }

    @Override
    public void insertSalPost(SalPost salPost) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.SAL_POST);
        sql.append(" (id, create_time, update_time, code, name) ")
                .append(" values (?,?,?,?,?) ");
        Object[] params = new Object[]{
                salPost.getId(), salPost.getCreateTime(), salPost.getUpdateTime(),
                salPost.getCode(), salPost.getName()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void updateSalPost(SalPost salPost) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.SAL_POST);
        sql.append(" set create_time=?, update_time=?, code=?, name=? ").append(" where id = ? ");
        Object[] params = new Object[]{
                salPost.getCreateTime(), salPost.getUpdateTime(),
                salPost.getCode(), salPost.getName(), salPost.getId()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void deleteSalPost(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete from ").append(TableName.SAL_POST)
                .append(" where id=? ");
        Object[] params = new Object[]{id};
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public List<SalPost> querySalPostAll(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select id,code,name,create_time,update_time ")
                .append(" from ").append(TableName.SAL_POST)
                .append(" where 1=1 ");
        if (!StringUtils.isEmpty(queryParam.get("id"))) {
            sql.append(" and id = ? ");
            paramList.add(queryParam.get("id"));
        }
        sql.append(" order by code ");
        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalPost.class), paramList.toArray());
    }

    @Override
    public List<Map<String, Object>> querySalPersonPostRefPage(PaginationDTO paginationDTO) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT a.id \"personTypeId\",a.code \"personTypeCode\",a.name \"personTypeName\", ")
                .append(" c.id \"postId\",c.code \"postCode\",c.name \"postName\" FROM ").append(TableName.SAL_PERSON_TYPE)
                .append(" a LEFT JOIN ").append(TableName.SAL_PERSON_POST_REF)
                .append(" b ON a.id = b.person_type_id LEFT JOIN ").append(TableName.SAL_POST)
                .append(" c ON b.post_id = c.id ")
                .append(" order by c.code ");
        String querySql = this.boxUpSql(sql.toString());
        Object[] params = boxUpParam(paramList.toArray(), paginationDTO);
        return this.getjdbcTemplate().queryForList(querySql, params);
    }

    @Override
    public int querySalPersonPostRefCount() {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT count(*) FROM ").append(TableName.SAL_PERSON_TYPE)
                .append(" a LEFT JOIN ").append(TableName.SAL_PERSON_POST_REF)
                .append(" b ON a.id = b.person_type_id LEFT JOIN ").append(TableName.SAL_POST)
                .append(" c ON b.post_id = c.id ");
        return this.getjdbcTemplate().queryForObject(sql.toString(), paramList.toArray(), Integer.class);
    }

    @Override
    public void deleteSalPersonPostRefByPersonTypeId(String personTypeId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete from ").append(TableName.SAL_PERSON_POST_REF)
                .append(" where person_type_id=? ");
        Object[] params = new Object[]{personTypeId};
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void insertSalPersonPostRef(SalPersonPostRef salPersonPostRef) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.SAL_PERSON_POST_REF);
        sql.append(" (id, person_type_id, post_id, create_time, update_time) ")
                .append(" values (?,?,?,?,?) ");
        Object[] params = new Object[]{
                salPersonPostRef.getId(), salPersonPostRef.getPersonTypeId(), salPersonPostRef.getPostId(),
                salPersonPostRef.getCreateTime(), salPersonPostRef.getUpdateTime()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public List<SalPost> querySalPostByPersonTypeId(String personTypeId) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select distinct b.id,b.code,b.name from ")
                .append(TableName.SAL_PERSON_POST_REF)
                .append(" a  JOIN ").append(TableName.SAL_POST)
                .append(" b on a.post_id=b.id where 1 = 1 ");
        if (!StringUtils.isEmpty(personTypeId)) {
            sql.append(" and a.person_type_id = ? ");
            paramList.add(personTypeId);
        }
        sql.append(" order by b.code");
        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalPost.class), paramList.toArray());
    }

}
